﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class ProjectControl_BLL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public Nullable<int> ConvertNullInt(Object o)
        {
            Nullable<int> result = (o == null ? null : (o.ToString().Length == 0 ? null : (Nullable<int>)Convert.ToInt32(o)));
            return result;
        }

        public Nullable<double> ConvertNullDouble(Object o)
        {
            Nullable<double> result = (o == null ? null : (o.ToString().Length == 0 ? null : (Nullable<double>)Convert.ToDouble(o)));
            return result;
        }

        public Nullable<DateTime> ConvertNullDateTime(Object o)
        {
            Nullable<DateTime> result = (o == null ? null : (o.ToString().Length == 0 ? null : (Nullable<DateTime>)Convert.ToDateTime(o)));
            return result;
        }

        public Nullable<TimeSpan> ConvertNullTimeSpan(Object o)
        {
            Nullable<TimeSpan> result = (o == null ? null : (o.ToString().Length == 0 ? null : (Nullable<TimeSpan>)Convert.ToDateTime(o).TimeOfDay));
            return result;
        }

        public DataTable GetFull(string bgm_year, string bgm_strat_list_seq, string bgm_source_seq
            , string job_site_seq, string bgm_exp_grp_seq)
        {
            try
            {
                string sql = "select ctrl.bgm_proj_ctrl_seq, ctrl.bgm_year, ctrl.bgm_source_seq"
                    + ", ctrl.bgm_strat_list_seq, ctrl.job_site_seq, expgrp.bgm_exp_grp_seq"
                    + ", expgrp.bgm_exp_grp_name, coalesce(ctrlexp.amount, 0) as amount"
                    + " from bgm_mas_expense_group expgrp"
                    + " left join bgm_project_control ctrl on expgrp.bgm_exp_grp_seq = ctrl.bgm_exp_grp_seq"
                        + (bgm_year.Length > 0 ? " and ctrl.bgm_year = " + bgm_year : "")
                        + (bgm_strat_list_seq.Length > 0 ? " and ctrl.bgm_strat_list_seq = " + bgm_strat_list_seq : "")
                        + (bgm_source_seq.Length > 0 ? " and ctrl.bgm_source_seq = " + bgm_source_seq : "")
                        + (job_site_seq.Length > 0 ? " and ctrl.job_site_seq = " + job_site_seq : "")
                        + (bgm_exp_grp_seq.Length > 0 ? " and ctrl.bgm_exp_grp_seq = " + bgm_exp_grp_seq : "")
                    + " left join (select bgm_proj_ctrl_seq"
                        + ", sum(coalesce(amount, 0)) as amount"
                        + " from bgm_project_control_expense"
                        + " group by bgm_proj_ctrl_seq"
                        + ") ctrlexp on ctrl.bgm_proj_ctrl_seq = ctrlexp.bgm_proj_ctrl_seq"
                    + " where expgrp.record_status not like 'D'";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable FindProjectCtrl(string bgm_year, string bgm_strat_list_seq, string bgm_source_seq
            , string job_site_seq, string bgm_exp_grp_seq)
        {
            try
            {
                string sql = "select ctrl.bgm_proj_ctrl_seq, ctrl.bgm_year, ctrl.bgm_source_seq"
                    + ", ctrl.bgm_strat_list_seq, ctrl.job_site_seq, ctrl.bgm_exp_grp_seq"
                    + ", coalesce(ctrlexp.amount, 0) as amount"
                    + " from bgm_project_control ctrl"
                    + " left join (select bgm_proj_ctrl_seq"
                        + ", sum(coalesce(amount, 0)) as amount"
                        + " from bgm_project_control_expense"
                        + " group by bgm_proj_ctrl_seq"
                        + ") ctrlexp on ctrl.bgm_proj_ctrl_seq = ctrlexp.bgm_proj_ctrl_seq"
                    + " where 0 = 0"
                    + (bgm_year.Length > 0 ? " and ctrl.bgm_year = " + bgm_year : "")
                    + (bgm_strat_list_seq.Length > 0 ? " and ctrl.bgm_strat_list_seq = " + bgm_strat_list_seq : "")
                    + (bgm_source_seq.Length > 0 ? " and ctrl.bgm_source_seq = " + bgm_source_seq : "")
                    + (job_site_seq.Length > 0 ? " and ctrl.job_site_seq = " + job_site_seq : "")
                    + (bgm_exp_grp_seq.Length > 0 ? " and ctrl.bgm_exp_grp_seq = " + bgm_exp_grp_seq : "");

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataRow GetInfo(string bgm_proj_ctrl_seq)
        {
            try
            {
                string sql = "select ctrl.bgm_proj_ctrl_seq, ctrl.bgm_year, ctrl.bgm_source_seq"
                    + ", ctrl.bgm_strat_list_seq, ctrl.job_site_seq, ctrl.bgm_exp_grp_seq"
                    + ", ctrlexp.amount"
                    + " from bgm_project_control ctrl"
                    + " left join (select bgm_proj_ctrl_seq"
                        + ", sum(coalesce(amount, 0)) as amount"
                        + " from bgm_project_control_expense"
                        + " group by bgm_proj_ctrl_seq"
                        + ") ctrlexp on ctrl.bgm_proj_ctrl_seq = ctrlexp.bgm_proj_ctrl_seq"
                    + " where ctrl.bgm_proj_ctrl_seq = " + bgm_proj_ctrl_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt.Rows[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string Insert_BgmProjectControl(string bgm_year, string bgm_source_seq
            , string bgm_strat_list_seq, string job_site_seq, string bgm_exp_grp_seq
            , string created_by)
        {
            try
            {
                Nullable<int> _bgm_year = ConvertNullInt(bgm_year);
                Nullable<int> _bgm_source_seq = ConvertNullInt(bgm_source_seq);
                Nullable<int> _bgm_strat_list_seq = ConvertNullInt(bgm_strat_list_seq);
                Nullable<int> _job_site_seq = ConvertNullInt(job_site_seq);
                Nullable<int> _bgm_exp_grp_seq = ConvertNullInt(bgm_exp_grp_seq);
                Nullable<int> _created_by = ConvertNullInt(created_by);

                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@bgm_year", _bgm_year);
                dbManager.AddParameters(1, "@bgm_source_seq", _bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_strat_list_seq", _bgm_strat_list_seq);
                dbManager.AddParameters(3, "@job_site_seq", _job_site_seq);
                dbManager.AddParameters(4, "@bgm_exp_grp_seq", _bgm_exp_grp_seq);
                dbManager.AddParameters(5, "@created_by", _created_by);
                dbManager.AddParameters(6, "@created_when", DateTime.Now);

                string strsql = "insert into bgm_project_control"
                    + " (bgm_year"
                        + ", bgm_source_seq"
                        + ", bgm_strat_list_seq"
                        + ", job_site_seq"
                        + ", bgm_exp_grp_seq"
                        + ", created_by"
                        + ", created_when"
                    + ")"
                    + " values (@bgm_year"
                        + ", @bgm_source_seq"
                        + ", @bgm_strat_list_seq"
                        + ", @job_site_seq"
                        + ", @bgm_exp_grp_seq"
                        + ", @created_by"
                        + ", @created_when"
                    + ")";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                string strsqlLast_insert = "select distinct scope_identity() FROM bgm_project_control";
                DataSet ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strsqlLast_insert);
                dbManager.CommitTransaction();
                return ds.Tables[0].Rows[0][0].ToString();
            }
            catch
            {
                dbManager.RollBackTransaction();
                return "-1";
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update_BgmProjectControl(string bgm_proj_ctrl_seq, string bgm_year, string bgm_source_seq
            , string bgm_strat_list_seq, string job_site_seq, string bgm_exp_grp_seq, string updated_by)
        {
            try
            {
                Nullable<int> _bgm_proj_ctrl_seq = ConvertNullInt(bgm_proj_ctrl_seq);
                Nullable<int> _bgm_year = ConvertNullInt(bgm_year);
                Nullable<int> _bgm_source_seq = ConvertNullInt(bgm_source_seq);
                Nullable<int> _bgm_strat_list_seq = ConvertNullInt(bgm_strat_list_seq);
                Nullable<int> _job_site_seq = ConvertNullInt(job_site_seq);
                Nullable<int> _bgm_exp_grp_seq = ConvertNullInt(bgm_exp_grp_seq);
                Nullable<int> _updated_by = ConvertNullInt(updated_by);

                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@bgm_proj_ctrl_seq", _bgm_proj_ctrl_seq);
                dbManager.AddParameters(1, "@bgm_year", _bgm_year);
                dbManager.AddParameters(2, "@bgm_source_seq", _bgm_source_seq);
                dbManager.AddParameters(3, "@bgm_strat_list_seq", _bgm_strat_list_seq);
                dbManager.AddParameters(4, "@job_site_seq", _job_site_seq);
                dbManager.AddParameters(5, "@bgm_exp_grp_seq", _bgm_exp_grp_seq);
                dbManager.AddParameters(6, "@updated_by", _updated_by);
                dbManager.AddParameters(7, "@updated_when", DateTime.Now);

                string strsql = "update bgm_project_control"
                    + " SET bgm_year = @bgm_year"
                        + ", bgm_source_seq = @bgm_source_seq"
                        + ", bgm_strat_list_seq = @bgm_strat_list_seq"
                        + ", job_site_seq = @job_site_seq"
                        + ", bgm_exp_grp_seq = @bgm_exp_grp_seq"
                        + ", updated_by = @updated_by"
                        + ", updated_when = @updated_when"
                    + " WHERE bgm_proj_ctrl_seq = @bgm_proj_ctrl_seq";
                
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete_BgmProjectControl()
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="bgm_proj_seq"></param>
        /// <param name="ctrl_type">1=รับโอน 2=โอนออก</param>
        /// <param name="bgm_proj_exp_seq"></param>
        /// <param name="amount"></param>
        /// <param name="created_by"></param>
        /// <returns></returns>
        public string Insert_BgmProjectControlExpense(string bgm_proj_ctrl_seq, string ctrl_type
            , string bgm_proj_exp_seq, string amount, string created_by)
        {
            try
            {
                Nullable<int> _bgm_proj_ctrl_seq = ConvertNullInt(bgm_proj_ctrl_seq);
                Nullable<int> _bgm_proj_exp_seq = ConvertNullInt(bgm_proj_exp_seq);
                Nullable<double> _amount = ConvertNullDouble(amount);
                Nullable<int> _created_by = ConvertNullInt(created_by);

                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@bgm_proj_ctrl_seq", _bgm_proj_ctrl_seq);
                dbManager.AddParameters(1, "@ctrl_type", ctrl_type);
                dbManager.AddParameters(2, "@bgm_proj_exp_seq", _bgm_proj_exp_seq);
                dbManager.AddParameters(3, "@amount", _amount);
                dbManager.AddParameters(4, "@created_by", _created_by);
                dbManager.AddParameters(5, "@created_when", DateTime.Now);

                string strsql = "insert into bgm_project_control_expense"
                    + " (bgm_proj_ctrl_seq"
                        + ", ctrl_type"
                        + ", bgm_proj_exp_seq"
                        + ", amount"
                        + ", created_by"
                        + ", created_when"
                    + ")"
                    + " values (@bgm_proj_ctrl_seq"
                        + ", @ctrl_type"
                        + ", @bgm_proj_exp_seq"
                        + ", @amount"
                        + ", @created_by"
                        + ", @created_when"
                    + ");"
                    + " SELECT scope_identity()";

                string strsqlLast_insert = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0].Rows[0][0].ToString();

                //"select distinct scope_identity() FROM bgm_project_control_expense";
                //DataSet ds = new DataSet();
                //ds = dbManager.ExecuteDataSet(CommandType.Text, strsqlLast_insert);
                dbManager.CommitTransaction();
                return strsqlLast_insert;
            }
            catch
            {
                dbManager.RollBackTransaction();
                return "-1";
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public string Insert_BgmProjectControlExpense(string bgm_proj_ctrl_seq, string ctrl_type
        //    , string bgm_proj_exp_seq, string amount, string created_by)
        //{
        //    try
        //    {
        //        Nullable<int> _bgm_proj_ctrl_seq = ConvertNullInt(bgm_proj_ctrl_seq);
        //        Nullable<int> _bgm_proj_exp_seq = ConvertNullInt(bgm_proj_exp_seq);
        //        Nullable<double> _amount = ConvertNullDouble(amount);
        //        Nullable<int> _created_by = ConvertNullInt(created_by);

        //        dbManager.Open();
        //        dbManager.BeginTransaction();
        //        dbManager.CreateParameters(6);
        //        dbManager.AddParameters(0, "@bgm_proj_ctrl_seq", _bgm_proj_ctrl_seq);
        //        dbManager.AddParameters(1, "@ctrl_type", ctrl_type);
        //        dbManager.AddParameters(2, "@bgm_proj_exp_seq", _bgm_proj_exp_seq);
        //        dbManager.AddParameters(3, "@amount", _amount);
        //        dbManager.AddParameters(4, "@created_by", _created_by);
        //        dbManager.AddParameters(5, "@created_when", DateTime.Now);

        //        string strsql = "insert into bgm_project_control_expense"
        //            + " (bgm_proj_ctrl_seq"
        //                + ", ctrl_type"
        //                + ", bgm_proj_exp_seq"
        //                + ", amount"
        //                + ", created_by"
        //                + ", created_when"
        //            + ")"
        //            + " values (@bgm_proj_ctrl_seq"
        //                + ", @ctrl_type"
        //                + ", @bgm_proj_exp_seq"
        //                + ", @amount"
        //                + ", @created_by"
        //                + ", @created_when"
        //            + ")";

        //        dbManager.ExecuteNonQuery(CommandType.Text, strsql);

        //        string strsqlLast_insert = "select distinct scope_identity() FROM bgm_project_control_expense";
        //        DataSet ds = new DataSet();
        //        ds = dbManager.ExecuteDataSet(CommandType.Text, strsqlLast_insert);
        //        dbManager.CommitTransaction();
        //        return ds.Tables[0].Rows[0][0].ToString();
        //    }
        //    catch
        //    {
        //        dbManager.RollBackTransaction();
        //        return "-1";
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}

        //public void Update_BgmProjectControlExpense(string bgm_proj_ctrl_exp_seq, string bgm_proj_ctrl_seq
        //    , string ctrl_type, string bgm_exp_seq, string amount, string updated_by)
        //{
        //    try
        //    {
        //        Nullable<int> _bgm_proj_ctrl_exp_seq = ConvertNullInt(bgm_proj_ctrl_exp_seq);
        //        Nullable<int> _bgm_proj_ctrl_seq = ConvertNullInt(bgm_proj_ctrl_seq);
        //        Nullable<int> _bgm_exp_seq = ConvertNullInt(bgm_exp_seq);
        //        Nullable<double> _amount = ConvertNullDouble(amount);
        //        Nullable<int> _updated_by = ConvertNullInt(updated_by);

        //        dbManager.Open();
        //        dbManager.BeginTransaction();
        //        dbManager.CreateParameters(7);
        //        dbManager.AddParameters(0, "@bgm_proj_ctrl_exp_seq", _bgm_proj_ctrl_exp_seq);
        //        dbManager.AddParameters(1, "@bgm_proj_ctrl_seq", _bgm_proj_ctrl_seq);
        //        dbManager.AddParameters(2, "@ctrl_type", ctrl_type);
        //        dbManager.AddParameters(3, "@bgm_exp_seq", _bgm_exp_seq);
        //        dbManager.AddParameters(4, "@amount", _amount);
        //        dbManager.AddParameters(5, "@updated_by", _updated_by);
        //        dbManager.AddParameters(6, "@updated_when", DateTime.Now);

        //        string strsql = "update bgm_project_control_expense"
        //            + " SET bgm_proj_ctrl_seq = @bgm_proj_ctrl_seq"
        //                + ", ctrl_type = @ctrl_type"
        //                + ", bgm_exp_seq = @bgm_exp_seq"
        //                + ", amount = @amount"
        //                + ", updated_by = @updated_by"
        //                + ", updated_when = @updated_when"
        //            + " WHERE bgm_proj_ctrl_exp_seq = @bgm_proj_ctrl_exp_seq";
                
        //        dbManager.ExecuteNonQuery(CommandType.Text, strsql);
        //    }
        //    catch
        //    {
        //        dbManager.RollBackTransaction();
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}

        public void Delete_BgmProjectControlExpense()
        {
        }
    }
}
